# frozen_string_literal: true

class UpdateVulnerabilityReadsTriggerToSetHasIssue < Gitlab::Database::Migration[2.1]
  enable_lock_retries!

  def up
    execute(<<~SQL)
    CREATE OR REPLACE FUNCTION insert_or_update_vulnerability_reads()
      RETURNS trigger
      LANGUAGE plpgsql
    AS $$
    DECLARE
      severity smallint;
      state smallint;
      report_type smallint;
      resolved_on_default_branch boolean;
      present_on_default_branch boolean;
      namespace_id bigint;
      has_issues boolean;
    BEGIN
      IF (NEW.vulnerability_id IS NULL AND (TG_OP = 'INSERT' OR TG_OP = 'UPDATE')) THEN
        RETURN NULL;
      END IF;

      IF (TG_OP = 'UPDATE' AND OLD.vulnerability_id IS NOT NULL AND NEW.vulnerability_id IS NOT NULL) THEN
        RETURN NULL;
      END IF;

      SELECT
        vulnerabilities.severity, vulnerabilities.state, vulnerabilities.report_type, vulnerabilities.resolved_on_default_branch, vulnerabilities.present_on_default_branch
      INTO
        severity, state, report_type, resolved_on_default_branch, present_on_default_branch
      FROM
        vulnerabilities
      WHERE
        vulnerabilities.id = NEW.vulnerability_id;

      IF present_on_default_branch IS NOT true THEN
        RETURN NULL;
      END IF;

      SELECT
        projects.namespace_id
      INTO
        namespace_id
      FROM
        projects
      WHERE
        projects.id = NEW.project_id;

      SELECT
        EXISTS (SELECT 1 FROM vulnerability_issue_links WHERE vulnerability_issue_links.vulnerability_id = NEW.vulnerability_id)
      INTO
        has_issues;

      INSERT INTO vulnerability_reads (vulnerability_id, namespace_id, project_id, scanner_id, report_type, severity, state, resolved_on_default_branch, uuid, location_image, cluster_agent_id, casted_cluster_agent_id, has_issues)
        VALUES (NEW.vulnerability_id, namespace_id, NEW.project_id, NEW.scanner_id, report_type, severity, state, resolved_on_default_branch, NEW.uuid::uuid, NEW.location->>'image', NEW.location->'kubernetes_resource'->>'agent_id', CAST(NEW.location->'kubernetes_resource'->>'agent_id' AS bigint), has_issues)
        ON CONFLICT(vulnerability_id) DO NOTHING;
      RETURN NULL;
    END
    $$
    SQL

    execute(<<~SQL)
    CREATE OR REPLACE FUNCTION insert_vulnerability_reads_from_vulnerability()
      RETURNS trigger
      LANGUAGE plpgsql
    AS $$
    DECLARE
      scanner_id bigint;
      uuid uuid;
      location_image text;
      cluster_agent_id text;
      casted_cluster_agent_id bigint;
      namespace_id bigint;
      has_issues boolean;
    BEGIN
      SELECT
        v_o.scanner_id, v_o.uuid, v_o.location->>'image', v_o.location->'kubernetes_resource'->>'agent_id', CAST(v_o.location->'kubernetes_resource'->>'agent_id' AS bigint), projects.namespace_id
      INTO
        scanner_id, uuid, location_image, cluster_agent_id, casted_cluster_agent_id, namespace_id
      FROM
        vulnerability_occurrences v_o
      INNER JOIN projects ON projects.id = v_o.project_id
      WHERE
        v_o.vulnerability_id = NEW.id
      LIMIT 1;

      SELECT
        EXISTS (SELECT 1 FROM vulnerability_issue_links WHERE vulnerability_issue_links.vulnerability_id = NEW.id)
      INTO
        has_issues;

      INSERT INTO vulnerability_reads (vulnerability_id, namespace_id, project_id, scanner_id, report_type, severity, state, resolved_on_default_branch, uuid, location_image, cluster_agent_id, casted_cluster_agent_id, has_issues)
        VALUES (NEW.id, namespace_id, NEW.project_id, scanner_id, NEW.report_type, NEW.severity, NEW.state, NEW.resolved_on_default_branch, uuid::uuid, location_image, cluster_agent_id, casted_cluster_agent_id, has_issues)
        ON CONFLICT(vulnerability_id) DO NOTHING;
      RETURN NULL;
    END
    $$
    SQL
  end

  def down
    execute(<<~SQL)
      CREATE OR REPLACE FUNCTION insert_or_update_vulnerability_reads() RETURNS trigger
          LANGUAGE plpgsql
          AS $$
      DECLARE
        severity smallint;
        state smallint;
        report_type smallint;
        resolved_on_default_branch boolean;
        present_on_default_branch boolean;
        namespace_id bigint;
      BEGIN
        IF (NEW.vulnerability_id IS NULL AND (TG_OP = 'INSERT' OR TG_OP = 'UPDATE')) THEN
          RETURN NULL;
        END IF;

        IF (TG_OP = 'UPDATE' AND OLD.vulnerability_id IS NOT NULL AND NEW.vulnerability_id IS NOT NULL) THEN
          RETURN NULL;
        END IF;

        SELECT
          vulnerabilities.severity, vulnerabilities.state, vulnerabilities.report_type, vulnerabilities.resolved_on_default_branch, vulnerabilities.present_on_default_branch
        INTO
          severity, state, report_type, resolved_on_default_branch, present_on_default_branch
        FROM
           vulnerabilities
        WHERE
          vulnerabilities.id = NEW.vulnerability_id;

        IF present_on_default_branch IS NOT true THEN
          RETURN NULL;
        END IF;

        SELECT
          projects.namespace_id
        INTO
          namespace_id
        FROM
          projects
        WHERE
          projects.id = NEW.project_id;

        INSERT INTO vulnerability_reads (vulnerability_id, namespace_id, project_id, scanner_id, report_type, severity, state, resolved_on_default_branch, uuid, location_image, cluster_agent_id, casted_cluster_agent_id)
          VALUES (NEW.vulnerability_id, namespace_id, NEW.project_id, NEW.scanner_id, report_type, severity, state, resolved_on_default_branch, NEW.uuid::uuid, NEW.location->>'image', NEW.location->'kubernetes_resource'->>'agent_id', CAST(NEW.location->'kubernetes_resource'->>'agent_id' AS bigint))
          ON CONFLICT(vulnerability_id) DO NOTHING;
        RETURN NULL;
      END
      $$;
    SQL

    execute(<<~SQL)
      CREATE OR REPLACE FUNCTION insert_vulnerability_reads_from_vulnerability() RETURNS trigger
          LANGUAGE plpgsql
          AS $$
      DECLARE
        scanner_id bigint;
        uuid uuid;
        location_image text;
        cluster_agent_id text;
        casted_cluster_agent_id bigint;
        namespace_id bigint;
      BEGIN
        SELECT
          v_o.scanner_id, v_o.uuid, v_o.location->>'image', v_o.location->'kubernetes_resource'->>'agent_id', CAST(v_o.location->'kubernetes_resource'->>'agent_id' AS bigint), projects.namespace_id
        INTO
          scanner_id, uuid, location_image, cluster_agent_id, casted_cluster_agent_id, namespace_id
        FROM
          vulnerability_occurrences v_o
        INNER JOIN projects ON projects.id = v_o.project_id
        WHERE
          v_o.vulnerability_id = NEW.id
        LIMIT 1;

        INSERT INTO vulnerability_reads (vulnerability_id, namespace_id, project_id, scanner_id, report_type, severity, state, resolved_on_default_branch, uuid, location_image, cluster_agent_id, casted_cluster_agent_id)
          VALUES (NEW.id, namespace_id, NEW.project_id, scanner_id, NEW.report_type, NEW.severity, NEW.state, NEW.resolved_on_default_branch, uuid::uuid, location_image, cluster_agent_id, casted_cluster_agent_id)
          ON CONFLICT(vulnerability_id) DO NOTHING;
        RETURN NULL;
      END
      $$;
    SQL
  end
end
